package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Dept;

public class DeptDao {

	private int bands = 5; // 一页显示数量

	/**
	 * 根据部门id获取部门信息
	 */
	public Dept getDeptByDeptId(String userName) throws SQLException {
		String sql = "select * from dept where dept_id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, userName);
		Dept dept = null;
		if (data != null) {
			dept = buildDept(data);
		}
		return dept;
	}

	/**
	 * 新建用户
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public boolean addDept(Dept d) throws SQLException {

		// 创建
		String sql = "insert into dept ( dept_name, dept_manager, dept_parent) values ( ?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql, d.getDeptName(), d.getDeptManager(), d.getDeptParent());

	}

	/**
	 * 保存部门
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public boolean saveDept(Dept d) throws SQLException {

		// 更新
		String sql = "update dept set dept_name=?, dept_manager = ?,dept_parent = ?  where dept_id=?";
		return Jdbc.getInstace().executeUpdate(sql, d.getDeptName(), d.getDeptManager(), d.getDeptParent(),
				d.getDeptId());

	}

	/**
	 * 根据ID删除部门
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteDeptById(String id) throws SQLException {
		String sql = "update dept set dept_status = 0  where dept_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}

	/**
	 * 根据ID修改部门
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean updateDeptById(String id) throws SQLException {
		String sql = "update dept set dept_status = 0  where dept_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}

	/**
	 * 根据学员的类型获取学员列表
	 * 
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getList() throws SQLException {
		List<Map<String, Object>> listData = null;
		String sql = "select * from dept where dept_status = 1 order by dept_id desc  ";
		listData = Jdbc.getInstace().executeQueryForList(sql);
		List<Dept> userList = null;
		if (listData != null && listData.size() > 0) {
			userList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				userList.add(new Dept(Integer.parseInt(data.get("dept_id").toString()), (String) data.get("dept_name"),
						(String) data.get("dept_manager"), (String) data.get("dept_parent"),
						Integer.parseInt(data.get("dept_status").toString())));
			}
		}
		return userList;
	}

	// /**
	// * 查询所有的部门
	// *
	// * @throws SQLException
	// */
	// public List<Dept> getAllDeptList( ) throws SQLException {
	// String sql = "select * from dept";
	// List<Object> params = new ArrayList<>();
	// List<Map<String, Object>> listData = null;
	// listData = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
	// List<Dept> userList = null;
	// if (listData != null && listData.size() > 0) {
	// userList = new ArrayList<>();
	// for (Map<String, Object> data : listData) {
	// userList.add(new Dept(
	// Integer.parseInt(data.get("dept_id").toString()),
	// (String)data.get("dept_name"),
	// (String)data.get("dept_manager"),
	// (String)data.get("dept_parent"),
	// Integer.parseInt(data.get("dept_status").toString())
	// ));
	// }
	// }
	// return userList;
	// }

	/**
	 * 带条件的分页查询
	 * 
	 * @param type
	 * @param page
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getPageDeptListByType(int page, String deptName, String deptManager, String deptParent)
			throws SQLException {
		int start = (page - 1) * bands;
		String sql = "select * from dept where dept_status = 1 ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, deptName, deptManager, deptParent);
		sql += " order by dept_id desc LIMIT ? , ?";
		params.add(start);
		params.add(bands);
		List<Map<String, Object>> listData = null;
		listData = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<Dept> userList = null;
		if (listData != null && listData.size() > 0) {
			userList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				userList.add(new Dept(Integer.parseInt(data.get("dept_id").toString()), (String) data.get("dept_name"),
						(String) data.get("dept_manager"), (String) data.get("dept_parent"),
						Integer.parseInt(data.get("dept_status").toString())));
			}
		}
		return userList;
	}

	/**
	 * 带条件获取部门的分页总数
	 */
	public int getDeptPageSum(String deptName, String deptManager, String deptParent) throws SQLException {
		String sql = "SELECT COUNT(dept_id) as sum from dept where dept_status = 1";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, deptName, deptManager, deptParent);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		int sum = 0;
		if (map != null) {
			sum = Integer.parseInt(map.get("sum").toString());
			sum = sum % bands > 0 ? sum / bands + 1 : sum / bands;
		}
		return sum;
	}

	private String buildSqlAndParams(List<Object> params, String sql, String deptID, String deptManager,
			String deptParent) {
		if (!StringUtils.isNullOrEmpty(deptID) || !StringUtils.isNullOrEmpty(deptManager)
				|| !StringUtils.isNullOrEmpty(deptParent)) {
			sql += " and";
		}
		if (!StringUtils.isNullOrEmpty(deptID)) {
			sql += " dept_name like ? ";
			params.add("%" + deptID + "%");
			if (!StringUtils.isNullOrEmpty(deptManager)) {
				sql += " and ";
			}
		}

		if (!StringUtils.isNullOrEmpty(deptManager)) {
			sql += " dept_manager like ? ";
			params.add("%" + deptManager + "%");
			if (!StringUtils.isNullOrEmpty(deptParent)) {
				sql += " and ";
			}
		}
		if (!StringUtils.isNullOrEmpty(deptParent)) {
			sql += " dept_parent like ? ";
			params.add("%" + deptParent + "%");
			System.out.println(deptParent);
		}
		return sql;
	}

	private Dept buildDept(Map<String, Object> data) {
		Dept dapt = new Dept();
		dapt.setDeptId(Integer.parseInt(data.get("dept_id").toString()));
		dapt.setDeptName((String) data.get("dept_name"));
		dapt.setDeptManager((String) data.get("dept_manager"));
		dapt.setDeptParent((String) data.get("dept_parent"));
		dapt.setDeptId(Integer.parseInt(data.get("dept_id").toString()));
		return dapt;
	}

}
